﻿CREATE PROCEDURE [util].[GetWaitStats]
@ResetBaseline BIT=0, @CollectionTime INT=10
AS
BEGIN

	DECLARE @RC int;
	SET @RC = 0;
	
	IF NOT EXISTS(
		SELECT * FROM [util].[WaitStatsBaseline]
	)
	OR (@ResetBaseline = 1)
	BEGIN
		EXEC @RC = [util].[RefreshWaitStats];
		IF(@RC <> 0) GOTO EXIT_PROC;
		
		DECLARE @DelayLength CHAR(8);
		SET @DelayLength = [util].[GetCharTimeFromSeconds](@CollectionTime);
		IF(@DelayLength IS NULL)
		BEGIN
			RAISERROR('Collection time set to default 10s',1,1);
			SET @DelayLength = '00:10';
		END;
		WAITFOR DELAY @DelayLength;
	END;

	DECLARE @CurrentTime datetime;
    SET @CurrentTime = CURRENT_TIMESTAMP;
    
	SELECT 
		dmv.[wait_type]
		,(dmv.[waiting_tasks_count] - bas.[WaitingTasksCount]) AS 'Waits'
		,(dmv.[wait_time_ms] - bas.[WaitTimeMS]) AS 'WaitTime'
		,(dmv.[signal_wait_time_ms] - bas.[SignalWaitTimeMS]) AS 'SignalWaitTime'
		,(@CurrentTime - bas.[BaselineDate]) AS 'ElapsedTime'
	FROM sys.dm_os_wait_stats AS dmv
	INNER JOIN [util].[WaitStatsBaseline] AS bas
		ON (dmv.[wait_type] COLLATE Latin1_General_CI_AS = bas.[WaitType])
	WHERE  (dmv.[wait_time_ms] - bas.[WaitTimeMS] > 0)
		AND(dmv.[wait_type] NOT IN (
				'CLR_SEMAPHORE'
				,'LAZYWRITER_SLEEP'
				,'RESOURCE_QUEUE'
				,'SLEEP_TASK'
				,'SLEEP_SYSTEMTASK'
				,'SQLTRACE_BUFFER_FLUSH'
				,'WAITFOR'
				)
			);
	
    SELECT
		bas.[SessionID]
		,bas.[HostName]
		,bas.[ProgramName]
		,(dmv.cpu_time - bas.[CPUTime]) AS 'CPUTime'
		,(dmv.total_scheduled_time - bas.[TotalScheduledTime]) AS 'TotalScheduleTime'
		,(dmv.total_elapsed_time - bas.[TotalElapsedTime]) AS 'ElapsedTime'
		,(dmv.reads - bas.[Reads]) AS 'PIO'
		,(dmv.writes - bas.[Writes]) AS 'Writes'
		,(dmv.logical_reads - bas.[LogicalReads]) AS 'LIO'
    FROM sys.dm_exec_sessions AS dmv
    INNER JOIN [util].[ProcessStatsBaseline] AS bas
		ON (bas.[SessionID] = dmv.[session_id])
		AND(bas.[HostProcessID] = dmv.[host_process_id])
	ORDER BY
		bas.[SessionID]
		,bas.[HostProcessID];

EXIT_PROC:
	RETURN(@RC);
END